﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Documents;
using QQTools.Core.Enums.ForwardGroupMessage;
using QQTools.Data.Enums.AddGroup;
using QQTools.Data.Models;
using QQTools.Logging;

namespace QQTools.Data.Repositories.AddGroup
{
    public class AddGroupRepository
    {
        private readonly string _connectionString;

        public AddGroupRepository(string connectionString)
        {
            _connectionString = connectionString;
        }

        /// <summary>
        /// 获取所有记录
        /// </summary>
        /// <returns>记录列表</returns>
        public async Task<List<AddGroupModel>> GetAllAsync()
        {
            var addGroups = new List<AddGroupModel>();

            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand("SELECT * FROM add_group", connection))
                {
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            addGroups.Add(MapTo(reader));
                        }
                    }
                }
            }

            return addGroups;
        }

        /// <summary>
        /// 根据 ID 获取记录
        /// </summary>
        /// <param name="id">记录 ID</param>
        /// <returns>记录</returns>
        public async Task<AddGroupModel> GetByIdAsync(int id)
        {
            AddGroupModel model = null;
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    using (var command = new SQLiteCommand("SELECT * FROM add_group WHERE id = @Id", connection))
                    {
                        command.Parameters.AddWithValue("@Id", id);
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            if (await reader.ReadAsync())
                            {
                                model = MapTo(reader);
                            }
                        }
                    }
                }
                LogManager.Info($"GetByIdAsync 成功获取 id={id} 的记录。");
            }
            catch (Exception ex)
            {
                LogManager.Error("GetByIdAsync 异常：" + ex);

            }
            return model;
        }

        /// <summary>
        /// 根据 QQ 和 GroupNumber 获取记录
        /// </summary>
        /// <param name="qq">QQ 号</param>
        /// <param name="groupNumber">群号</param>
        /// <returns>记录列表</returns>
        public async Task<List<AddGroupModel>> GetByQQAndGroupNumberAsync(string qq, string groupNumber)
        {
            var addGroups = new List<AddGroupModel>();

            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand("SELECT * FROM add_group WHERE qq = @QQ AND group_number = @GroupNumber", connection))
                {
                    command.Parameters.AddWithValue("@QQ", qq);
                    command.Parameters.AddWithValue("@GroupNumber", groupNumber);
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            addGroups.Add(MapTo(reader));
                        }
                    }
                }
            }

            return addGroups;
        }

        /// <summary>
        /// 根据 GroupNumber 获取记录
        /// </summary>
        /// <param name="groupNumber">群号</param>
        /// <returns>记录列表</returns>
        public async Task<List<AddGroupModel>> GetByGroupNumberAsync(string groupNumber)
        {
            var addGroups = new List<AddGroupModel>();

            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand("SELECT * FROM add_group WHERE group_number = @GroupNumber", connection))
                {
                    command.Parameters.AddWithValue("@GroupNumber", groupNumber);
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            addGroups.Add(MapTo(reader));
                        }
                    }
                }
            }

            return addGroups;
        }

        /// <summary>
        /// 插入记录
        /// </summary>
        /// <param name="addGroup">记录</param>
        public async Task AddAsync(AddGroupModel addGroup)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand(@"
                    INSERT INTO add_group (qq, group_number, status)
                    VALUES (@QQ, @GroupNumber, @Status);", connection))
                {
                    command.Parameters.AddWithValue("@QQ", addGroup.QQ);
                    command.Parameters.AddWithValue("@GroupNumber", addGroup.GroupNumber);
                    command.Parameters.AddWithValue("@Status", addGroup.Status);

                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        /// <summary>
        /// 更新记录
        /// </summary>
        /// <param name="addGroup">记录</param>
        public async Task UpdateAsync(AddGroupModel addGroup)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand(@"
                    UPDATE add_group
                    SET qq = @QQ, group_number = @GroupNumber, status = @Status, update_time = CURRENT_TIMESTAMP
                    WHERE id = @Id;", connection))
                {
                    command.Parameters.AddWithValue("@Id", addGroup.Id);
                    command.Parameters.AddWithValue("@QQ", addGroup.QQ);
                    command.Parameters.AddWithValue("@GroupNumber", addGroup.GroupNumber);
                    command.Parameters.AddWithValue("@Status", addGroup.Status);

                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        /// <summary>
        /// 根据 ID 删除记录
        /// </summary>
        /// <param name="id">记录 ID</param>
        public async Task DeleteAsync(int id)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand("DELETE FROM add_group WHERE id = @Id;", connection))
                {
                    command.Parameters.AddWithValue("@Id", id);
                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        /// <summary>
        /// 清空表
        /// </summary>
        public async Task ClearTableAsync()
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand("DELETE FROM add_group;", connection))
                {
                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        /// <summary>
        /// 插入或更新记录
        /// </summary>
        /// <param name="addGroup">记录</param>
        public async Task AddOrUpdateAsync(AddGroupModel addGroup)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        // 检查记录是否已经存在
                        using (var checkCommand = new SQLiteCommand("SELECT id FROM add_group WHERE group_number = @GroupNumber", connection, transaction))
                        {
                            checkCommand.Parameters.AddWithValue("@GroupNumber", addGroup.GroupNumber);
                            var existingId = await checkCommand.ExecuteScalarAsync();

                            if (existingId != null)
                            {
                                // 如果存在，则更新数据
                                int id = Convert.ToInt32(existingId);
                                addGroup.Id = id;
                                await UpdateAsync(addGroup, transaction);
                            }
                            else
                            {
                                // 如果不存在，则插入新数据
                                await AddAsync(addGroup, transaction);
                            }
                        }
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        LogManager.Error("[AddGroupRepository] AddOrUpdateAsync", ex);
                        transaction.Rollback();
                    }
                }
            }
        }

        /// <summary>
        /// 批量插入或更新记录
        /// </summary>
        /// <param name="addGroups"></param>
        /// <returns></returns>
        public async Task AddOrUpdateBatchAsync(List<AddGroupModel> addGroups)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        foreach (var addGroup in addGroups)
                        {
                            using (var command = new SQLiteCommand(@"
                        INSERT OR IGNORE INTO add_group (group_number, status, create_time, update_time)
                        VALUES (@GroupNumber, @Status, @CreateTime, @UpdateTime);", connection, transaction))
                            {
                                command.Parameters.AddWithValue("@GroupNumber", addGroup.GroupNumber);
                                command.Parameters.AddWithValue("@Status", addGroup.Status);
                                command.Parameters.AddWithValue("@CreateTime", addGroup.CreateTime);
                                command.Parameters.AddWithValue("@UpdateTime", addGroup.UpdateTime);
                                await command.ExecuteNonQueryAsync();
                            }
                        }
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        LogManager.Error("[AddGroupRepository] AddOrUpdateBatchAsync", ex);
                    }
                }
            }
        }



        /// <summary>
        /// 根据 GroupNumber 更新 QQ 和 Status
        /// </summary>
        /// <param name="groupNumber">群号</param>
        /// <param name="newQQ">新的 QQ 号</param>
        /// <param name="newStatus">新的状态</param>
        public async Task UpdateQQAndStatusByGroupNumberAsync(string groupNumber, string newQQ, int newStatus)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand(@"
                    UPDATE add_group
                    SET qq = @QQ, status = @Status, update_time = CURRENT_TIMESTAMP
                    WHERE group_number = @GroupNumber;", connection))
                {
                    command.Parameters.AddWithValue("@GroupNumber", groupNumber);
                    command.Parameters.AddWithValue("@QQ", newQQ);
                    command.Parameters.AddWithValue("@Status", newStatus);

                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        /// <summary>
        /// 获取一条按照 ID 升序且 Status 为 1 的记录并修改状态为 3
        /// </summary>
        /// <returns>记录</returns>
        public async Task<AddGroupModel> GetAndLockFirstByStatusAsync()
        {
            AddGroupModel addGroup = null;

            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        // 获取第一条按照 ID 升序且 Status 为 1 的记录
                        using (var selectCommand = new SQLiteCommand(@"
                            SELECT * FROM add_group
                            WHERE status = 1
                            ORDER BY id ASC
                            LIMIT 1;", connection, transaction))
                        {
                            using (var reader = await selectCommand.ExecuteReaderAsync())
                            {
                                if (await reader.ReadAsync())
                                {
                                    addGroup = new AddGroupModel
                                    {
                                        Id = reader.GetInt32(reader.GetOrdinal("id")),
                                        QQ = reader.IsDBNull(reader.GetOrdinal("qq")) ? null : reader.GetString(reader.GetOrdinal("qq")),
                                        GroupNumber = reader.GetString(reader.GetOrdinal("group_number")),
                                        Status = (AddGroupStatus)reader.GetInt32(reader.GetOrdinal("status")),
                                        CreateTime = reader.GetDateTime(reader.GetOrdinal("create_time")),
                                        UpdateTime = reader.GetDateTime(reader.GetOrdinal("update_time"))
                                    };
                                }
                            }
                        }

                        if (addGroup != null)
                        {
                            // 更新记录的状态为 3
                            using (var updateCommand = new SQLiteCommand(@"
                                UPDATE add_group
                                SET status = @status, update_time = CURRENT_TIMESTAMP
                                WHERE id = @Id;", connection, transaction))
                            {
                                updateCommand.Parameters.AddWithValue("@status", (int)AddGroupStatus.Running);
                                updateCommand.Parameters.AddWithValue("@Id", addGroup.Id);
                                await updateCommand.ExecuteNonQueryAsync();
                            }
                        }

                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        LogManager.Error("[AddGroupRepository] GetAndLockFirstByStatusAsync", ex);
                        transaction.Rollback();
                        throw;
                    }
                }
            }

            return addGroup;
        }

        private async Task AddAsync(AddGroupModel addGroup, SQLiteTransaction transaction)
        {
            using (var command = new SQLiteCommand(@"
                INSERT INTO add_group (qq, group_number, status)
                VALUES (@QQ, @GroupNumber, @Status);", transaction.Connection, transaction))
            {
                command.Parameters.AddWithValue("@QQ", addGroup.QQ);
                command.Parameters.AddWithValue("@GroupNumber", addGroup.GroupNumber);
                command.Parameters.AddWithValue("@Status", addGroup.Status);

                await command.ExecuteNonQueryAsync();
            }
        }

        private async Task UpdateAsync(AddGroupModel addGroup, SQLiteTransaction transaction)
        {
            using (var command = new SQLiteCommand(@"
                UPDATE add_group
                SET qq = @QQ, group_number = @GroupNumber, status = @Status, update_time = CURRENT_TIMESTAMP
                WHERE id = @Id;", transaction.Connection, transaction))
            {
                command.Parameters.AddWithValue("@Id", addGroup.Id);
                command.Parameters.AddWithValue("@QQ", addGroup.QQ);
                command.Parameters.AddWithValue("@GroupNumber", addGroup.GroupNumber);
                command.Parameters.AddWithValue("@Status", addGroup.Status);

                await command.ExecuteNonQueryAsync();
            }
        }
        /// <summary>
        /// 将所有状态为 3 的记录设置为 1
        /// </summary>
        public async Task ResetStatusToPendingAsync()
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand(@"
                    UPDATE add_group
                    SET status = 1, update_time = CURRENT_TIMESTAMP
                    WHERE status = 3;", connection))
                {
                    await command.ExecuteNonQueryAsync();
                }
            }
        }


        /// <summary>
        /// 根据  group_number 修改 status 的值
        /// </summary>
        /// <param name="groupNumber">群号</param>
        /// <param name="status">状态</param>
        /// <returns></returns>
        public async Task UpdateStatusByGroupNumberAsync(string groupNumber, AddGroupStatus status)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand(@"
                    UPDATE add_group
                    SET status = @Status, update_time = CURRENT_TIMESTAMP
                    WHERE group_number = @GroupNumber;", connection))
                {
                    command.Parameters.AddWithValue("@GroupNumber", groupNumber);
                    command.Parameters.AddWithValue("@Status", (int)status);

                    await command.ExecuteNonQueryAsync();
                }
            }
        }


        /// <summary>
        /// 根据QQ获取记录
        /// </summary>
        public async Task<List<AddGroupModel>> GetByQQAsync(string qq)
        {
            var list = new List<AddGroupModel>();
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    string sql = "SELECT * FROM add_group WHERE qq = @qq";
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@qq", qq);
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                list.Add(MapTo(reader));
                            }
                        }
                    }
                }
                LogManager.Info($"GetByQQAsync 成功获取 QQ={qq} 的记录。");
            }
            catch (Exception ex)
            {
                LogManager.Error("GetByQQAsync 异常：" + ex);
                throw;
            }
            return list;
        }


        /// <summary>
        /// 将 SqlDataReader 映射为 AddGroupModel 对象
        /// </summary>
        private AddGroupModel MapTo(System.Data.Common.DbDataReader reader)
        {
            return new AddGroupModel
            {
                Id = reader.GetInt32(reader.GetOrdinal("id")),
                QQ = reader.IsDBNull(reader.GetOrdinal("qq")) ? null : reader.GetString(reader.GetOrdinal("qq")),
                GroupNumber = reader.GetString(reader.GetOrdinal("group_number")),
                Status = (AddGroupStatus)reader.GetInt32(reader.GetOrdinal("status")),
                CreateTime = reader.GetDateTime(reader.GetOrdinal("create_time")),
                UpdateTime = reader.GetDateTime(reader.GetOrdinal("update_time"))
            };
        }


        /// <summary>
        /// 批量插入记录：如果记录已存在（基于 qq 和 group_number），则跳过
        /// </summary>
        /// <param name="models">待插入的 AddGroupModel 集合</param>
        public async Task BulkInsertAsync(List<AddGroupModel> models)
        {
            if (models == null || models.Count == 0)
            {
                LogManager.Info("BulkInsertAsync：传入的记录集合为空，无需插入。");
                return;
            }

            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    using (var transaction = connection.BeginTransaction())
                    {
                        foreach (var model in models)
                        {
                            // 使用 INSERT ... SELECT 结合 WHERE NOT EXISTS 判断记录是否存在
                            string sql = @"
                                INSERT INTO add_group 
                                (qq, group_number, status, create_time, update_time)
                                SELECT @qq, @group_number, @status, @create_time, @update_time
                                WHERE NOT EXISTS (
                                    SELECT 1 FROM add_group WHERE qq = @qq AND group_number = @group_number
                             );";

                            using (var command = new SQLiteCommand(sql, connection, transaction))
                            {
                                command.Parameters.AddWithValue("@qq", model.QQ);
                                command.Parameters.AddWithValue("@group_number", model.GroupNumber);
                                command.Parameters.AddWithValue("@status", (int)model.Status);
                                command.Parameters.AddWithValue("@create_time", model.CreateTime);
                                command.Parameters.AddWithValue("@update_time", model.UpdateTime);

                                await command.ExecuteNonQueryAsync();
                            }
                        }
                        transaction.Commit();
                    }
                }
                LogManager.Info("BulkInsertAsync 成功批量插入记录（存在重复记录时跳过）。");
            }
            catch (Exception ex)
            {
                LogManager.Error("BulkInsertAsync 异常：" + ex);

            }
        }

        /// <summary>
        /// 删除记录:根据群号删除
        /// </summary>
        /// <param name="groupNumber"></param>
        /// <exception cref="NotImplementedException"></exception>
        public bool DeleteByGroupNumberAsync(string groupNumber)
        {
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    connection.Open();
                    using (var command = new SQLiteCommand("DELETE FROM add_group WHERE group_number = @GroupNumber", connection))
                    {
                        command.Parameters.AddWithValue("@GroupNumber", groupNumber);
                        command.ExecuteNonQuery();
                    }
                }
                LogManager.Info($"DeleteByGroupNumberAsync 成功删除群号为 {groupNumber} 的记录。");
                return true;
            }
            catch (Exception ex)
            {
                LogManager.Error("DeleteByGroupNumberAsync 异常：" + ex);
                return false;
            }
        }
    }
}